package cn.telling.product.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import cn.telling.product.dao.IAreaInfoDao;
import cn.telling.product.vo.AreaInfoVo;
import cn.telling.utils.AutoInjection;
import cn.telling.utils.BaseUtil;

/**
 * 
 * @ClassName: AreaInfoDaoImpl 
 * @Description: 区域信息数据服务接口的实现
 * @author 陈赟 
 * @date 2015年6月17日 下午4:00:59 
 *
 */
@Repository("areaInfoDao")
public class AreaInfoDaoImpl implements IAreaInfoDao {
    
    private static Logger LOGGER = LoggerFactory.getLogger(AreaInfoDaoImpl.class);
    
    @Autowired
    @Qualifier("film-template")
    private JdbcTemplate jdbcTemplate;

    @Override
    public AreaInfoVo getAreaInfoByAreaId(String areaId) {
        String sql = "select * from AreaInfo where AreaId=?";
        Object[] args = new Object[] { areaId };
        
        LOGGER.debug("根据区域ID获取区域信息：" + BaseUtil.logSQL(sql, args));
        List<AreaInfoVo> li = jdbcTemplate.query(sql, args,
                new RowMapper<AreaInfoVo>() {
                    public AreaInfoVo mapRow(ResultSet rs, int i)
                            throws SQLException {
                        AreaInfoVo vo = new AreaInfoVo();
                        AutoInjection.Rs2Vo(rs, vo, null);
                        return vo;
                    }
                });
        if (li != null && li.size() > 0) {
            return li.get(0);
        }
        return null;
    }

    @Override
    public AreaInfoVo getAreaInfoByUserId(String userId) {
        String sql = " select af.areaname, af.areaid, af.areaLevel, af.parentid " +
                " from supply su " +
                " inner join supply_area sa on sa.supply_id = su.id and su.user_id = ? " +
                " left  join areainfo af on af.areaid = sa.area_id ";
   
       Object[] params = new Object[]{userId};
       LOGGER.debug("根据供应商id获得用户所在区域: " + BaseUtil.logSQL(sql,params)); 
       List<AreaInfoVo> list = jdbcTemplate.query(sql, new Object[]{userId}, new RowMapper<AreaInfoVo>(){
           public AreaInfoVo mapRow(ResultSet rs,int i) throws SQLException{
               AreaInfoVo vo = new AreaInfoVo();
               vo.setAreaName(rs.getString("areaname"));
               vo.setAreaId(rs.getBigDecimal("areaid"));
               vo.setAreaLevel(rs.getBigDecimal("areaLevel"));
               vo.setParentId(rs.getBigDecimal("parentid"));
               return vo;
           }
       });
   
       if(list !=  null && list.size() > 0){
           return list.get(0);
       }
   
       return null;
    }

    @Override
    public List<AreaInfoVo> getAreaLsAll() {
        String sql = "select ai.areaid,ai.areaname,ai.parentid,ai.areacode,ai.arealevel from areainfo ai where status='1' ";
        
        LOGGER.debug("查询区域表:"+BaseUtil.logSQL(sql, new Object[]{}));
        
        List<AreaInfoVo> areaInfoVoList = jdbcTemplate.query(sql,new RowMapper<AreaInfoVo>() {
            public AreaInfoVo mapRow(ResultSet rs, int rowNum)throws SQLException {
                AreaInfoVo vo = new AreaInfoVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        
        return areaInfoVoList;
    }

	@Override
	public List<AreaInfoVo> getAreaLsAllBySupplyId(String supplyId) {
		StringBuffer sb=new StringBuffer();
		sb.append(" with areainfos as(");
		sb.append("  select  ai.areaid,ai.areaname,ai.parentid,ai.areacode,ai.arealevel,ai.status from areainfo ai");
		sb.append("  start with ai.areaid in ( select sa.area_id from supply_area sa where sa.supply_id=? )");
		sb.append("  connect by prior ai.areaid=ai.parentid)");
		sb.append(" select a.areaid,a.areaname,a.parentid,a.areacode,a.arealevel from areainfos a where a.status='1'");
		
        LOGGER.debug("查询区域表:"+BaseUtil.logSQL(sb.toString(), new Object[]{supplyId}));
        
        List<AreaInfoVo> areaInfoVoList = jdbcTemplate.query(sb.toString(),new Object[]{supplyId},new RowMapper<AreaInfoVo>() {
            public AreaInfoVo mapRow(ResultSet rs, int rowNum)throws SQLException {
                AreaInfoVo vo = new AreaInfoVo();
                AutoInjection.Rs2Vo(rs, vo, null);
                return vo;
            }
        });
        return areaInfoVoList;
    }

}
